﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.OleDb;
using System.Text;

namespace Agenda.Repositories
{
    public class ConfigurationRepository
    {
        #region Public Methods

        public void CreateDatabase()
        {
            var cat = new ADOX.Catalog();
            
            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataConnection.DBConn + "; Jet OLEDB:Engine Type=5");
            
            cat = null;
        }

        public void CreateTables()
        {
            using (OleDbConnection conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataConnection.DBConn + ";"))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    DataConnection.Open(conn);
                    cmd.Connection = conn;

                    //Creo la tabla Person con todos los datos que puede contener una Persona
                    cmd.CommandText =
                        string.Format("CREATE TABLE Person ({0} integer IDENTITY (1, 1) PRIMARY KEY, {1} varchar(25), "
                        + "{2} varchar(25), {3} varchar(20), {4} varchar(20), {5} varchar(20), {6} varchar(50), {7} varchar(50), {8} varchar(100))"
                        , DBObjects.Person.userId.ToString()
                        , DBObjects.Person.name.ToString()
                        , DBObjects.Person.surname.ToString()
                        , DBObjects.Person.cellphone1.ToString()
                        , DBObjects.Person.cellphone2.ToString()
                        , DBObjects.Person.telephone.ToString()
                        , DBObjects.Person.obra.ToString()
                        , DBObjects.Person.email.ToString()
                        , DBObjects.Person.extraInformation.ToString());
                    cmd.ExecuteNonQuery();

                    //Creo la tabla Obra con todos los datos que contiene una Obra
                    cmd.CommandText =
                        string.Format("CREATE TABLE Obra ({0} integer IDENTITY (1, 1) PRIMARY KEY, {1} varchar(25), "
                        + "{2} varchar(15), {3} int, {4} varchar(20), {5} varchar(12), {6} varchar(15), {7} varchar(18), "
                        + "{8} varchar(20), {9} varchar(20), {10} varchar(20), {11} varchar(21), "
                        + "{12} varchar(15), {13} varchar(15), {14} int)"
                        , DBObjects.Obra.obraId.ToString()
                        , DBObjects.Obra.obraType.ToString()
                        , DBObjects.Obra.style.ToString()
                        , DBObjects.Obra.contractNumber.ToString()
                        , DBObjects.Obra.obraName.ToString()
                        , DBObjects.Obra.country.ToString()
                        , DBObjects.Obra.province.ToString()
                        , DBObjects.Obra.city.ToString()
                        , DBObjects.Obra.comitente.ToString()
                        , DBObjects.Obra.constructionCompany.ToString()
                        , DBObjects.Obra.subContractor.ToString()
                        , DBObjects.Obra.inspection.ToString()
                        , DBObjects.Obra.initiation.ToString()
                        , DBObjects.Obra.finish.ToString()
                        , DBObjects.Obra.amount.ToString()
                        );
                    cmd.ExecuteNonQuery();

                    //Creo la Tabla del Caño
                    cmd.CommandText =
                        string.Format("CREATE TABLE Spout ({0} int IDENTITY (1, 1) PRIMARY KEY, {1} int, "
                        + "{2} int, {3} int, {4} int, {5} int)"
                        , DBObjects.Spout.spoutId.ToString()
                        , DBObjects.Spout.obraId.ToString()
                        , DBObjects.Spout.diameter.ToString()
                        , DBObjects.Spout.pressure.ToString()
                        , DBObjects.Spout.rigidity.ToString()
                        , DBObjects.Spout.length.ToString()
                        );
                    cmd.ExecuteNonQuery();
                }
            }
        }

        #endregion
    }
}

